/**
 * @filename:MaterialServiceImpl Jan 14, 2025
 * @project Wonder Vue Admin Api  1.1.0
 * Copyright(c) 2022 Wonder Co. Ltd. 
 * All right reserved. 
 */
package com.wonder.app.service.impl;

import com.vtuzx.core.bean.dto.VtuzxMap;
import com.vtuzx.core.utils.VtuzxUtil;
import com.wonder.admin.service.IVtuzxKingdeeService;
import com.wonder.app.domain.Material;
import com.wonder.app.mapper.MaterialDao;
import com.wonder.app.service.MaterialService;
import com.wonder.app.request.MaterialRequest;
import com.wonder.bean.kingdee.VtuzxKingdeeCloudClient;
import com.wonder.bean.kingdee.xqc.VtuzxArrayList;
import com.wonder.common.utils.DateUtils;
import com.wonder.common.utils.StringUtils;

import java.time.LocalDate;
import java.time.ZoneId;
import java.util.*;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;

/**   
 * @Description:TODO(物料列表服务实现)
 *
 * @version: 1.1.0
 * @author: Wonder
 * 
 */
@Service
public class MaterialServiceImpl  extends ServiceImpl<MaterialDao, Material> implements MaterialService  {

	private static final Logger _logger = LoggerFactory.getLogger(MaterialServiceImpl.class);
	@Autowired
	@Qualifier("sqlServerJdbcTemplate")
	private JdbcTemplate sqlServerJdbcTemplate;

	@Autowired
	private IVtuzxKingdeeService vtuzxKingdeeService;

	@Autowired
	private MaterialDao materialDao;

	@Autowired
	private MaterialService materialService;
    /**
     * 验证物料列表信息
     * 
     * @param material
     * @param type
     * @return String
     */
    @Override
    public String validate(Material material,String type){
    
    
       return "";
    }
    
    
    /**
	 * Description:物料列表分页返回
	 * @param request //检索条件
	 * @param page //分页参数
	 * @return IPage<Material>   //分页记录
	 */
	@Override 
	public IPage<Material> selectList(MaterialRequest request, Page<Material> page){
	    LambdaQueryWrapper<Material> lambdaQueryWrapper = new LambdaQueryWrapper<>();
	    
	    if (null != request.getId()) {
	       lambdaQueryWrapper.eq(Material::getId, request.getId());
	      }
		if (StringUtils.hasText(request.getMaterialNumber())) {
			lambdaQueryWrapper.like(Material::getMaterialNumber, request.getMaterialNumber());
		}
		if (StringUtils.hasText(request.getMaterialName())) {
			lambdaQueryWrapper.like(Material::getMaterialName, request.getMaterialName());
		}
		if (StringUtils.hasText(request.getMaterialGroupName())) {
			lambdaQueryWrapper.like(Material::getMaterialGroupName, request.getMaterialGroupName());
		}
		if (StringUtils.hasText(request.getMaterialGroupNumber())) {
			lambdaQueryWrapper.like(Material::getMaterialGroupNumber, request.getMaterialGroupNumber());
		}

		if (StringUtils.hasText(request.getStockNumber())) {
			lambdaQueryWrapper.like(Material::getStockNumber, request.getStockNumber());
		}
		if (StringUtils.hasText(request.getStockName())) {
			lambdaQueryWrapper.like(Material::getStockName, request.getStockName());
		}

		if (request.getCreateTime() != null) {
			Date[] fCREATEDATE = request.getCreateTime();
			if (fCREATEDATE.length > 0) {
				// 设置开始时间为当天的零点
				Calendar startCalendar = Calendar.getInstance();
				startCalendar.setTime(fCREATEDATE[0]);
				startCalendar.set(Calendar.HOUR_OF_DAY, 0);
				startCalendar.set(Calendar.MINUTE, 0);
				startCalendar.set(Calendar.SECOND, 0);
				startCalendar.set(Calendar.MILLISECOND, 0);
				Date startDate = startCalendar.getTime();
				lambdaQueryWrapper.ge(Material::getCreateTime, startDate);
			}
			if (fCREATEDATE.length == 2) {
				// 设置结束时间为当天的24点
				Calendar endCalendar = Calendar.getInstance();
				endCalendar.setTime(fCREATEDATE[1]);
				endCalendar.set(Calendar.HOUR_OF_DAY, 23);
				endCalendar.set(Calendar.MINUTE, 59);
				endCalendar.set(Calendar.SECOND, 59);
				endCalendar.set(Calendar.MILLISECOND, 999);
				Date endDate = endCalendar.getTime();
				lambdaQueryWrapper.le(Material::getCreateTime, endDate);
			}
		}


		lambdaQueryWrapper.orderByDesc(Material::getId);	
		Page<Material> selectPage = this.baseMapper.selectPage(page, lambdaQueryWrapper);
		return selectPage;
    }

	@Override
	public void syncMaterial() throws Exception {
		syncMaterial(0,100);
	}

	/**
	 * 根据物料编码查询金蝶数据库
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> getMaterialByNo(List<String> materialNoList) throws Exception {
		String placeholders = String.join(",", Collections.nCopies(materialNoList.size(), "?"));
		String sql = "SELECT  t1.FNUMBER,t2.FNAME,t2.FSPECIFICATION,t4.FNUMBER 'unitNo'" +
				",t5.FNAME 'unitName',t1.FDOCUMENTSTATUS ,t1.FFORBIDSTATUS,t6.FISBATCHMANAGE" +
				" from dbo.T_BD_MATERIAL t1 " +
				"left join T_BD_MATERIAL_L t2 on t1.FMATERIALID = t2.FMATERIALID " +
				"left join t_bd_materialbase t3 on t1.FMATERIALID = t3.FMATERIALID " +
				"left join T_BD_UNIT t4 on t4.FUNITID = t3.FBASEUNITID " +
				"left join T_BD_UNIT_L t5 on t4.FUNITID = t5.FUNITID " +
				"left join T_BD_MATERIALSTOCK t6 on t1.FMATERIALID = t6.FMATERIALID " +
				"where t1.FNUMBER in (" + placeholders + ")";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, materialNoList.toArray());
	}

	/**
	 * 根据车间名称查询车间编码
	 * @param departNameList
	 * @return
	 * @throws Exception
	 */
	@Override
	public Map<String, Object> getDepartNo(List<String> departNameList) throws Exception {
		String placeholders = String.join(",", Collections.nCopies(departNameList.size(), "?"));
		String sql = "select t1.FNUMBER,t2.FNAME from T_BD_DEPARTMENT t1 " +
				"left join T_BD_DEPARTMENT_L t2  on t1.FDEPTID = t2.FDEPTID " +
				"where t2.FNAME in (" + placeholders + ")";
		// 执行查询
		List<Map<String,Object>> departist = sqlServerJdbcTemplate.queryForList(sql, departNameList.toArray());
		Map<String,Object> dataMap = new HashMap<>();
		for (Map<String,Object> data : departist){
			String FNUMBER = VtuzxUtil.getString(data,"FNUMBER");
			String FNAME = VtuzxUtil.getString(data,"FNAME");
			dataMap.put(FNAME,FNUMBER);
		}
		return dataMap;
	}

	/**
	 * 根据客户名称获取客户编码
	 * @param cusTomerNameList
	 * @return
	 * @throws Exception
	 */
	@Override
	public Map<String, Object> getCustomerName(List<String> cusTomerNameList) throws Exception {
		String placeholders = String.join(",", Collections.nCopies(cusTomerNameList.size(), "?"));
		String sql = "select t1.FNUMBER,t2.FNAME from T_BD_CUSTOMER t1 " +
				"left join T_BD_CUSTOMER_L t2 " +
				"on t1.FCUSTID = t2.FCUSTID " +
				"where t2.FNAME in (" + placeholders + ")";
		// 执行查询
		List<Map<String,Object>> departist = sqlServerJdbcTemplate.queryForList(sql, cusTomerNameList.toArray());
		Map<String,Object> dataMap = new HashMap<>();
		for (Map<String,Object> data : departist){
			String FNUMBER = VtuzxUtil.getString(data,"FNUMBER");
			String FNAME = VtuzxUtil.getString(data,"FNAME");
			dataMap.put(FNAME,FNUMBER);
		}
		return dataMap;
	}

	/**
	 * 数据库添加销售订单导入的数据
	 * @param dataList
	 * @return
	 * @throws Exception
	 */
	@Override
	public void saveSaleOrder(String userName,Long user_id,List<Map<String,Object>> dataList) throws Exception {
		dataList = dataList.subList(1,dataList.size());
		final int batchSize = 50;
		String sql = "insert into deyuanOrder.dbo.t_sale_order " +
				"(CUSBILLNO,ORDERDATE,WEEK,CUSTOMER," +
				"TYNO,QTY,PRICE,TAXRATE,TOTALPRICE,CURRENCY,USER_ID,TYPE,LINE,SHIPWEEK)" +
				" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		for (int i = 0; i < dataList.size(); i += batchSize) {
			int end = Math.min(i + batchSize, dataList.size());
			List<Map<String, Object>> subList = dataList.subList(i, end);

			List<Object[]> batchArgs = new ArrayList<>();
			for (Map<String, Object> row : subList) {
				Object[] args = new Object[]{
						VtuzxUtil.getString(row,"__EMPTY"),
						VtuzxUtil.getInt(row,"__EMPTY_1"),
						VtuzxUtil.getInt(row,"__EMPTY_2"),
						VtuzxUtil.getString(row,"__EMPTY_3"),//客户
						VtuzxUtil.getString(row,"销售订单"),//TY件号
						VtuzxUtil.getDouble(row,"__EMPTY_5"),//数量
						VtuzxUtil.getDouble(row,"__EMPTY_6"),//含税单价
						VtuzxUtil.getDouble(row,"__EMPTY_7"),//税率
						VtuzxUtil.getDouble(row,"__EMPTY_8"),//价税合计
						VtuzxUtil.getDouble(row,"__EMPTY_14"),//币别
						user_id,
						"saleOrder",
						VtuzxUtil.getInt(row,"line"),//行号
						VtuzxUtil.getInt(row,"__EMPTY_9")//船期

				};
				batchArgs.add(args);
			}
			// 打印参数组（可选）
			_logger.info(">>> 插入条数: " + batchArgs.size());
//			for (Object[] args : batchArgs) {
//				System.out.println("参数: " + Arrays.toString(args));
//			}

			int[] ints = sqlServerJdbcTemplate.batchUpdate(sql, batchArgs);
		}


	}

	/**
	 * 校验客户订单号不是空
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkCusBillNo(Long user_id) throws Exception {

		String sql = "select * from deyuanOrder.dbo.t_sale_order where CUSBILLNO = '' " +
				"and USER_ID = ? and TYPE = ?";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id,"saleOrder");

	}

	/**
	 * 校验销售订单中的物料信息
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkMaterial(Long user_id) throws Exception {
		//连表查询物料物料为空，未审核，或者不是未禁用的，或者金蝶不存在此物料如果查询到数据就说明有问题
		String sql = "select * from deyuanOrder.dbo.t_sale_order t1 " +
				"left join t_bd_material t2 " +
				"on t1.TYNO = t2.fnumber " +
				"where t1.USER_ID = ?  and t1.TYPE = 'saleOrder' " +
				"and (t2.FDOCUMENTSTATUS <> 'C'or t2.FFORBIDSTATUS <> 'A' or t1.TYNO = '' or t2.FNUMBER is null)";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	/**
	 * 校验客户是否为空，金蝶是否存在，状态是否是已审核
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkCustomer(Long user_id) throws Exception {
		String sql = "select * from deyuanOrder.dbo.t_sale_order t1 " +
				"left join T_BD_CUSTOMER_L t3 " +
				"on t1.CUSTOMER = t3.fname " +
				"left join T_BD_CUSTOMER t2 " +
				"on t3.FCUSTID = t2.FCUSTID " +
				"where t1.USER_ID = ?  and t1.TYPE = 'saleOrder' " +
				"and (t2.FDOCUMENTSTATUS <> 'C'or t2.FFORBIDSTATUS <> 'A' or t1.CUSTOMER = '' or t2.FNUMBER is null)";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	/**
	 * 校验数量是否都大于0
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkCount(Long user_id) throws Exception {
		String sql = "select * from deyuanOrder.dbo.t_sale_order t1 " +
				"where t1.USER_ID = ?  and t1.TYPE = 'saleOrder' " +
				"and  t1.QTY <= 0";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	/**
	 * 删除销售订单模板数据
	 * @param userName
	 * @param user_id
	 * @throws Exception
	 */
	@Override
	public void deleteSaleOrder(String userName, Long user_id) throws Exception {
		String sql = "delete from deyuanOrder.dbo.t_sale_order " +
				"where USER_ID = ? and TYPE = 'saleOrder'";

		// 执行查询
		sqlServerJdbcTemplate.update(sql, user_id);
	}

	/**
	 * 删除生产订单的数据
	 * @param userName
	 * @param user_id
	 * @throws Exception
	 */
	@Override
	public void deleteOperpOrder(String userName, Long user_id) throws Exception {
		String sql = "delete from deyuanOrder.dbo.t_sfc_operp " +
				"where USER_ID = ? and TYPE = 'sfcOperp'";

		// 执行查询
		sqlServerJdbcTemplate.update(sql, user_id);
	}

	/**
	 * 保存生产订单导入的数据到sqlserver
	 * @param userName
	 * @param user_id
	 * @param map
	 */
	@Override
	public void saveSfcOperp(String userName, Long user_id, List<Map<String, Object>> map) {
		map = map.subList(1,map.size());
		final int batchSize = 50;
		String sql = "insert into deyuanOrder.dbo.t_sfc_operp " +
				"(BILLNO,MATERIALNO,LINE,USER_ID,TYPE,MATERIALTYPE,BOMNO,ORDERTIME,STARTTIME,ENDTIME,WORKSHOP)" +
				" values (?,?,?,?,?,?,?,?,?,?,?)";

		for (int i = 0; i < map.size(); i += batchSize) {
			int end = Math.min(i + batchSize, map.size());
			List<Map<String, Object>> subList = map.subList(i, end);
			List<Object[]> batchArgs = new ArrayList<>();
			for (Map<String, Object> row : subList) {
				Object[] args = new Object[]{
						VtuzxUtil.getString(row,"__EMPTY_2"),//指令单号
						VtuzxUtil.getString(row,"__EMPTY_4"),//物料编码
						VtuzxUtil.getInt(row,"line"),//行号
						user_id,
						"sfcOperp",
						VtuzxUtil.getString(row,"__EMPTY_9"),//产品类型
						VtuzxUtil.getString(row,"__EMPTY_10"),//bom版本
						VtuzxUtil.getInt(row,"__EMPTY_3"),//单据日期(时间戳)
						VtuzxUtil.getInt(row,"__EMPTY_6"),//开工时间(时间戳)
						VtuzxUtil.getInt(row,"__EMPTY_7"),//完工时间(时间戳)
						VtuzxUtil.getString(row,"__EMPTY_1")//车间
				};
				batchArgs.add(args);
			}
			// 打印参数组（可选）
			_logger.info(">>> 插入条数: " + batchArgs.size());
//			for (Object[] args : batchArgs) {
//				System.out.println("参数: " + Arrays.toString(args));
//			}

			int[] ints = sqlServerJdbcTemplate.batchUpdate(sql, batchArgs);
		}
	}


	@Override
	public List<Map<String, Object>> checkOperpBillNo(Long user_id) throws Exception {
		String sql = "select * from deyuanOrder.dbo.t_sfc_operp where BILLNO = '' " +
				"and USER_ID = ? and TYPE = ?";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id,"sfcOperp");
	}

	@Override
	public List<Map<String, Object>> checkOperpMaterial(Long user_id) throws Exception {
		//连表查询物料物料为空，未审核，或者不是未禁用的，或者金蝶不存在此物料如果查询到数据就说明有问题
		String sql = "select * from deyuanOrder.dbo.t_sfc_operp t1 " +
				"left join t_bd_material t2 " +
				"on t1.MATERIALNO = t2.fnumber " +
				"left join T_BD_MATERIALBASE t3 " +
				"on t2.FMATERIALID = t3.FMATERIALID " +
				"where t1.USER_ID = ?  and t1.TYPE = 'sfcOperp' " +
				"and (t2.FDOCUMENTSTATUS <> 'C'or t2.FFORBIDSTATUS <> 'A' or t1.MATERIALNO = '' or t2.FNUMBER is null or t3.FISPRODUCE = 0)";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	/**
	 * 校验bom
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkOperpBom(Long user_id) throws Exception {
		String sql = "select * from deyuanOrder.dbo.t_sfc_operp t1 " +
				"left join T_ENG_BOM t2 " +
				"on t1.BOMNO = t2.fnumber " +
				"left join T_BD_MATERIALBASE t3 " +
				"on t2.FMATERIALID = t3.FMATERIALID " +
				"where t1.USER_ID = ?  and t1.TYPE = 'sfcOperp' and t1.MATERIALTYPE = '外协' " +
				"and (t2.FDOCUMENTSTATUS <> 'C'or t2.FFORBIDSTATUS <> 'A' or t1.BOMNO = '' or t2.FNUMBER is null )";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	/**
	 * 校验车间
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkOperpWorkShop(Long user_id) throws Exception {
		String sql = "select * from deyuanOrder.dbo.t_sfc_operp t1 " +
				"left join T_BD_DEPARTMENT_L t3 " +
				"on t1.WORKSHOP = t3.FNAME " +
				"left join T_BD_DEPARTMENT t2 " +
				"on t2.FDEPTID = t3.FDEPTID " +
				"where t1.USER_ID = ?  and t1.TYPE = 'sfcOperp' " +
				"and (t2.FDOCUMENTSTATUS <> 'C' or t2.FFORBIDSTATUS <> 'A' or t1.WORKSHOP = '' or t2.FNUMBER is null )";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	/**
	 * 校验单据日期
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkOperpOrderTime(Long user_id) throws Exception {
		String sql = "select * from deyuanOrder.dbo.t_sfc_operp where ORDERTIME = 0 " +
				"and USER_ID = ? and TYPE = 'sfcOperp'";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	/**
	 * 校验开工时间,不能大于完工时间,不能小于单据日期
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkOperpStartTime(Long user_id) throws Exception {
		String sql = "select * from deyuanOrder.dbo.t_sfc_operp where (STARTTIME = 0 or STARTTIME < ORDERTIME or STARTTIME > ENDTIME ) " +
				"and USER_ID = ? and TYPE = 'sfcOperp'";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}


	/**
	 * 校验完工时间,不能小于单据日期,不能小于开工日期
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkOperpEndTime(Long user_id) throws Exception {
		String sql = "select * from deyuanOrder.dbo.t_sfc_operp " +
				"where (ENDTIME = 0 or ENDTIME < ORDERTIME or STARTTIME > ENDTIME ) " +
				"and USER_ID = ? and TYPE = 'sfcOperp'";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	/**
	 * 校验船期不能小于单据日期,不能为空，单据日期不能为空
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkSheep(Long user_id) throws Exception {
		String sql = "select * from deyuanOrder.dbo.t_sale_order " +
				"where (SHIPWEEK = 0 or SHIPWEEK < ORDERDATE or ORDERDATE = 0 ) " +
				"and USER_ID = ? and TYPE = 'saleOrder'";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}


	/**
	 * 查询币别编码
	 * @param currencyList
	 * @return
	 * @throws Exception
	 */
	@Override
	public Map<String, Object> getCurrencyNo(List<String> currencyList) throws Exception {
		String placeholders = String.join(",", Collections.nCopies(currencyList.size(), "?"));
		String sql = "select t1.FNUMBER,t2.FNAME from T_BD_CURRENCY t1 " +
				"left join T_BD_CURRENCY_L t2 " +
				"on t1.FCURRENCYID = t2.FCURRENCYID " +
				"where t2.FNAME in (" + placeholders + ")";

		// 执行查询
		_logger.info("查询币别的sql:" + sql);
		List<Map<String,Object>> departist = sqlServerJdbcTemplate.queryForList(sql, currencyList.toArray());
		Map<String,Object> dataMap = new HashMap<>();
		for (Map<String,Object> data : departist){
			String FNUMBER = VtuzxUtil.getString(data,"FNUMBER");
			String FNAME = VtuzxUtil.getString(data,"FNAME");
			dataMap.put(FNAME,FNUMBER);
		}
		return dataMap;
	}

	/**
	 * 同步金蝶物料
	 *
	 * @param start 开始行
	 * @param limit 获取件数
	 */
	private void syncMaterial(int start, int limit) throws Exception {
		VtuzxKingdeeCloudClient client = vtuzxKingdeeService.loginKingdee();
		List<VtuzxMap> materialQuery = vtuzxKingdeeService.query(client, "BD_MATERIAL", new VtuzxArrayList<>(
						"FName", //物料名称
						"FNumber", //物料编码
						"FSpecification", //规格
						"FBaseUnitId.FNumber", //基本单位编码
						"FBaseUnitId.FName", //基本单位名称
						"FStockId.FNumber", //仓库编码
						"FStockId.FName", //仓库名称
						"FCreateDate", //创建修改日期
						"FCreatorId.fname", //创建人
						"FIsBatchManage", //是否启用批号
						"FMaterialGroup.FNumber", //物料分组
						"FMaterialGroup.FName", //物料分组
						"FPurURNum", //分子
						"FPurURNom", //分母
						"FAuxUnitID.fname", //辅助单位名称
						"FAuxUnitID.FNumber"//辅助单位编码
				),
				"", "FMATERIALID", start, limit);

		List<Material> materialList = new ArrayList<>();
		for (VtuzxMap data : materialQuery){
			Material material = new Material();
			String materialNo = VtuzxUtil.getString(data,"FNumber");
			String materialName = VtuzxUtil.getString(data,"FName");
			String FSpecification = VtuzxUtil.getString(data,"FSpecification");
			String unitNo = VtuzxUtil.getString(data,"FBaseUnitId.FNumber");
			String unitName = VtuzxUtil.getString(data,"FBaseUnitId.FName");
			String stockName = VtuzxUtil.getString(data,"FStockId.FName");
			String stockNo = VtuzxUtil.getString(data,"FStockId.FNumber");
			String FCreateDate = VtuzxUtil.getString(data,"FCreateDate");
			Date createDate = DateUtils.strToDate(FCreateDate,"yyyy-MM-dd'T'HH:mm:ss");
			String createUserName = VtuzxUtil.getString(data,"FCreatorId.fname");
			Boolean isBatch = VtuzxUtil.getBoolean(data,"FIsBatchManage");
			String groupNo = VtuzxUtil.getString(data,"FMaterialGroup.FNumber");
			String groupName = VtuzxUtil.getString(data,"FMaterialGroup.FName");
			String auxNo = VtuzxUtil.getString(data,"FAuxUnitID.fname");
			String auxName = VtuzxUtil.getString(data,"FAuxUnitID.FNumber");
			Double FPurURNum = VtuzxUtil.getDouble(data,"FPurURNum");//分子
			Double FPurURNom = VtuzxUtil.getDouble(data,"FPurURNom");//分母

			material.setMaterialNumber(materialNo);
			material.setMaterialName(materialName);
			material.setSpecification(FSpecification);
			material.setUnitName(unitName);
			material.setUnitNumber(unitNo);
			material.setStockName(stockName);
			material.setStockNumber(stockNo);
			material.setCreateTime(createDate);
			material.setCreateUser(createUserName);
			material.setIsflot(isBatch);
			material.setMaterialGroupName(groupName);
			material.setMaterialGroupNumber(groupNo);
			material.setConversion(FPurURNum/FPurURNom);
			material.setFAuxUnitName(auxName);
			material.setFAuxUnitNo(auxNo);
			materialList.add(material);
//			materialDao.insert(material);
		}
		materialService.saveOrUpdateBatch(materialList);
		if (materialQuery.size() >= limit) {
			syncMaterial(start + limit, limit);
		}
	}

	/**
	 * 数据库添加销售出库单导入的数据
	 * @param detailList
	 * @return
	 * @throws Exception
	 */
	@Override
	public void saveSaleOutOrder(String userName, Long user_id, List<Map<String, Object>> detailList) throws Exception {
		detailList = detailList.subList(1, detailList.size());
		final int batchSize = 50;
		String sql = "insert into deyuanOrder.dbo.t_sale_out_order (CustNo,BillNo,TYNumber,Count,IsDate,USER_ID,TYPE,LINE) values (?,?,?,?,?,?,?,?)";

		Map<String, Object> headerRow = detailList.get(0);
		String custNo = VtuzxUtil.getString(headerRow, "__EMPTY_1"); // 转为字符串
		Object isDate = VtuzxUtil.getInt(headerRow, "__EMPTY_10");
		int lineCounter = 1;

		for (int i = 3; i < detailList.size(); i += batchSize) {
			int end = Math.min(i + batchSize, detailList.size());
			List<Map<String, Object>> subList = detailList.subList(i, end);
			List<Object[]> batchArgs = new ArrayList<>();

			for (Map<String, Object> row : subList) {
				String billNo = VtuzxUtil.getString(row, "__EMPTY_1");

				String tyNumber = VtuzxUtil.getString(row, "__EMPTY_2");


				Object[] args = new Object[]{
						custNo, // 已截断
						billNo, // 已截断
						tyNumber, // 已截断
						VtuzxUtil.getDouble(row, "__EMPTY_5"),
						isDate,
						user_id,
						"OutOrder",
						lineCounter++
				};
				batchArgs.add(args);
			}

			_logger.info(">>> 插入条数: " + batchArgs.size());
			int[] ints = sqlServerJdbcTemplate.batchUpdate(sql, batchArgs);


		}
	}

	/**
	 * 数据库添加销售出库单导入的数据
	 * @param detailList
	 * @return
	 * @throws Exception
	 */
	@Override
	public void saveSaleAOutOrder(String userName, Long user_id, List<Map<String, Object>> detailList) throws Exception {
		detailList = detailList.subList(1, detailList.size());
		final int batchSize = 50;
		String sql = "insert into deyuanOrder.dbo.t_sale_out_order (CustNo,BillNo,TYNumber,Count,IsDate,USER_ID,TYPE,LINE) values (?,?,?,?,?,?,?,?)";

		Map<String, Object> headerRow = detailList.get(0);
		String custNo = VtuzxUtil.getString(headerRow, "__EMPTY_1"); // 客户编码
		Object isDate = VtuzxUtil.getInt(headerRow, "__EMPTY_10");//日期
		int lineCounter = 1;


		for (int i = 3; i < detailList.size(); i += batchSize) {

			int end = Math.min(i + batchSize, detailList.size());
			List<Map<String, Object>> subList = detailList.subList(i, end);
			List<Object[]> batchArgs = new ArrayList<>();

			for (Map<String, Object> row : subList) {
				String billNo = VtuzxUtil.getString(row, "__EMPTY_1");
				String tyNumber = VtuzxUtil.getString(row, "__EMPTY_3");

				Object[] args = new Object[]{
						custNo, // 已截断
						billNo, // 已截断
						tyNumber, // 已截断
						VtuzxUtil.getDouble(row, "__EMPTY_5"),
						isDate,
						user_id,
						"OutOrder",
						lineCounter++
				};
				batchArgs.add(args);
			}

			_logger.info(">>> 插入条数: " + batchArgs.size());
			if (!batchArgs.isEmpty()) {
				sqlServerJdbcTemplate.batchUpdate(sql, batchArgs); // 执行插入
			}

		}
	}

	//删除该数据
	@Override
	public void deleteSaleOutOrder(String userName, Long user_id) throws Exception {
		String sql = "delete from deyuanOrder.dbo.t_sale_out_order " +
				"where USER_ID = ? and TYPE = 'OutOrder'";

		// 执行查询
		sqlServerJdbcTemplate.update(sql, user_id);
	}
	/**
	 * 校验客户订单号不是空并且销售订单中存在
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkCusBillNoout(Long user_id) throws Exception {
		// 修正后的 SQL
		String sql = "SELECT * " +
				"FROM deyuanOrder.dbo.t_sale_out_order t1 " +  // 正确表别名语法
				"LEFT JOIN T_SAL_ORDER t2 " +                  // 添加空格
				"ON t1.BillNo = t2.F_PSUH_Text_qtr " +         // 添加空格
				"WHERE (t1.BillNo = '' OR t2.F_PSUH_Text_qtr IS NULL or t2.FDOCUMENTSTATUS <> 'C') " +
				"AND t1.USER_ID = ? " +                        // 第一个参数占位符
				"AND t1.TYPE = 'OutOrder'";                // 直接使用固定值
		// 只传入一个参数
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	/**
	 * 校验销售订单中的物料信息
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkMaterialout(Long user_id) throws Exception {
		//连表查询物料物料为空，未审核，或者不是未禁用的，或者金蝶不存在此物料如果查询到数据就说明有问题
		String sql = "select * from deyuanOrder.dbo.t_sale_out_order t1 " +
				"left join t_bd_material t2 " +
				"on t1.TYNumber = t2.fnumber " +
				"where t1.USER_ID = ?  " +
				"AND t1.TYPE = 'OutOrder'"+
		        "And (t2.FDOCUMENTSTATUS <> 'C'or t2.FFORBIDSTATUS <> 'A' or t1.TYNumber = '' or t2.FNUMBER is null)";
		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	/**
	 * 校验客户是否为空，金蝶是否存在，状态是否是已审核
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkCustomerout(Long user_id) throws Exception {
		String sql = "SELECT * " +
				"FROM deyuanOrder.dbo.t_sale_out_order t1 " +
				"LEFT JOIN T_BD_CUSTOMER_L t3 ON t1.CustNo = t3.fname " +
				"LEFT JOIN T_BD_CUSTOMER t2 ON t3.FCUSTID = t2.FCUSTID " +
				"LEFT JOIN T_SAL_ORDER t4 ON t4.F_PSUH_Text_qtr = t1.BillNo " +
				"WHERE t1.USER_ID = ? " +
				"  AND t1.TYPE = 'OutOrder' " +
				"  AND ( " +  // 外层括号开始
				"        ( " +  // 条件组1开始
				"          t2.FDOCUMENTSTATUS <> 'C' " +
				"          OR t2.FFORBIDSTATUS <> 'A' " +
				"          OR t1.CustNo = '' " +
				"          OR t1.CustNo IS NULL " +
				"          OR t2.FNUMBER IS NULL " +
				"        ) " +  // 条件组1结束
				"        OR " +
				"        ( " +  // 条件组2开始
				"          t4.F_PSUH_BASE_QTR <> t3.FCUSTID " +  // 已修复空格
				"          OR (t4.F_PSUH_BASE_QTR IS NULL AND t3.FCUSTID IS NOT NULL) " +
				"          OR (t4.F_PSUH_BASE_QTR IS NOT NULL AND t3.FCUSTID IS NULL) " +
				"        ) " +  // 条件组2结束
				"      )";  // 外层括号结束

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}
	/**
	 * 校验数量是否都大于0
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkCountout(Long user_id) throws Exception {
		String sql = "select * from deyuanOrder.dbo.t_sale_out_order t1 " +
				"where t1.USER_ID = ?  " +
				"AND t1.TYPE = 'OutOrder'"+
				"and (t1.Count <= 0 or t1.Count <= 0.0) ";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}
	/**
	 * 校验数量大于订单时间
	 * @param user_id
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> checkIsTime(Long user_id) throws Exception {
		String sql = "select * from deyuanOrder.dbo.t_sale_out_order t1"+
				" left join T_SAL_ORDER t2" +
				" on  t1.BillNo=t2.F_PSUH_Text_qtr "+
				" where (t1.IsDate = 0 or t1.IsDate < ISNULL(DATEDIFF(DAY, '1900-01-01', t2.FDATE), 99999999))" +
				" and t1.USER_ID = ? "+
				" AND t1.TYPE = 'OutOrder'";
		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}


	/**
	 * 根据青岛物料编码查询金蝶数据库
	 * @return
	 * @throws Exception
	 */
	@Override
	public List<Map<String, Object>> getqingMaterialByNo(List<String> materialNoList) throws Exception {
		String placeholders = String.join(",", Collections.nCopies(materialNoList.size(), "?"));
		String sql = "SELECT  t1.FNUMBER,t2.FNAME,t2.FSPECIFICATION,t4.FNUMBER 'unitNo'" +
				",t5.FNAME 'unitName',t1.FDOCUMENTSTATUS ,t1.FFORBIDSTATUS" +
				" from AIS20250710155859.dbo.T_BD_MATERIAL t1 " +
				"left join AIS20250710155859.dbo.T_BD_MATERIAL_L t2 on t1.FMATERIALID = t2.FMATERIALID " +
				"left join AIS20250710155859.dbo.t_bd_materialbase t3 on t1.FMATERIALID = t3.FMATERIALID " +
				"left join AIS20250710155859.dbo.T_BD_UNIT t4 on t4.FUNITID = t3.FBASEUNITID " +
				"left join AIS20250710155859.dbo.T_BD_UNIT_L t5 on t4.FUNITID = t5.FUNITID " +
				"where t1.FNUMBER in (" + placeholders + ")";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, materialNoList.toArray());
	}
	/**
	 * 根据客户名称获取客户编码
	 * @param cusTomerNameList
	 * @return
	 * @throws Exception
	 */
	@Override
	public Map<String, Object> getqingCustomerName(List<String> cusTomerNameList) throws Exception {
		String placeholders = String.join(",", Collections.nCopies(cusTomerNameList.size(), "?"));
		String sql = "select t1.FNUMBER,t2.FNAME from AIS20250710155859.dbo.T_BD_CUSTOMER t1 " +
				"left join AIS20250710155859.dbo.T_BD_CUSTOMER_L t2 " +
				"on t1.FCUSTID = t2.FCUSTID " +
				"where t2.FNAME in (" + placeholders + ")";
		// 执行查询
		List<Map<String,Object>> departist = sqlServerJdbcTemplate.queryForList(sql, cusTomerNameList.toArray());
		Map<String,Object> dataMap = new HashMap<>();
		for (Map<String,Object> data : departist){
			String FNUMBER = VtuzxUtil.getString(data,"FNUMBER");
			String FNAME = VtuzxUtil.getString(data,"FNAME");
			dataMap.put(FNAME,FNUMBER);
		}
		return dataMap;
	}
	/**
	 * 根据车间名称查询车间编码
	 * @param departNameList
	 * @return
	 * @throws Exception
	 */
	@Override
	public Map<String, Object> getqingDepartNo(List<String> departNameList) throws Exception {
		String placeholders = String.join(",", Collections.nCopies(departNameList.size(), "?"));
		String sql = "select t1.FNUMBER,t2.FNAME from AIS20250710155859.dbo.T_BD_DEPARTMENT t1 " +
				"left join AIS20250710155859.dbo.T_BD_DEPARTMENT_L t2  on t1.FDEPTID = t2.FDEPTID " +
				"where t2.FNAME in (" + placeholders + ")";
		// 执行查询
		List<Map<String,Object>> departist = sqlServerJdbcTemplate.queryForList(sql, departNameList.toArray());
		Map<String,Object> dataMap = new HashMap<>();
		for (Map<String,Object> data : departist){
			String FNUMBER = VtuzxUtil.getString(data,"FNUMBER");
			String FNAME = VtuzxUtil.getString(data,"FNAME");
			dataMap.put(FNAME,FNUMBER);
		}
		return dataMap;
	}


	/**
	 * 查询币别编码
	 * @param currencyList
	 * @return
	 * @throws Exception
	 */
	@Override
	public Map<String, Object> getqingCurrencyNo(List<String> currencyList) throws Exception {
		String placeholders = String.join(",", Collections.nCopies(currencyList.size(), "?"));
		String sql = "select t1.FNUMBER,t2.FNAME from AIS20250710155859.dbo.T_BD_CURRENCY t1 " +
				"left join AIS20250710155859.dbo.T_BD_CURRENCY_L t2 " +
				"on t1.FCURRENCYID = t2.FCURRENCYID " +
				"where t2.FNAME in (" + placeholders + ")";

		// 执行查询
		_logger.info("查询币别的sql:" + sql);
		List<Map<String,Object>> departist = sqlServerJdbcTemplate.queryForList(sql, currencyList.toArray());
		Map<String,Object> dataMap = new HashMap<>();
		for (Map<String,Object> data : departist){
			String FNUMBER = VtuzxUtil.getString(data,"FNUMBER");
			String FNAME = VtuzxUtil.getString(data,"FNAME");
			dataMap.put(FNAME,FNUMBER);
		}
		return dataMap;
	}
	@Override
	public void saveQingSaleOrder(String userName, Long user_id, List<Map<String, Object>> dataList) throws Exception {
		dataList = dataList.subList(1,dataList.size());
		final int batchSize = 50;
		String sql = "insert into qingOrder.dbo.t_qing_sale_order " +
				"(CUSBILLNO,CusTomer,AccCustomer,Currency," +
				"InDate,QTY,PRICE,TAXRATE,MaterialNo,IsGift,USER_ID,TYPE,LINE,ShipDate)" +
				" values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		for (int i = 0; i < dataList.size(); i += batchSize) {
			int end = Math.min(i + batchSize, dataList.size());
			List<Map<String, Object>> subList = dataList.subList(i, end);

			List<Object[]> batchArgs = new ArrayList<>();
			for (Map<String, Object> row : subList) {

				Object[] args = new Object[]{
						VtuzxUtil.getString(row,"销售订单"),//客户订单号
						VtuzxUtil.getString(row,"__EMPTY_1"),//终端客户
						VtuzxUtil.getString(row,"__EMPTY_2"),//结算客户
						VtuzxUtil.getString(row,"__EMPTY_3"),//币别
						VtuzxUtil.getInt(row,"__EMPTY"),//日期
						VtuzxUtil.getDouble(row,"__EMPTY_5"),//数量
						VtuzxUtil.getDouble(row,"__EMPTY_6"),//含税单价
						VtuzxUtil.getDouble(row,"__EMPTY_7"),//税率
						VtuzxUtil.getString(row,"__EMPTY_4"),//物料编码
						VtuzxUtil.getString(row,"__EMPTY_8"),//是否为赠品
						user_id,
						"QsaleOrder",
						VtuzxUtil.getInt(row,"line"),//行号
						VtuzxUtil.getInt(row,"__EMPTY_9")//船期

				};
				batchArgs.add(args);
			}
			// 打印参数组（可选）
			_logger.info(">>> 插入条数: " + batchArgs.size());
//			for (Object[] args : batchArgs) {
//				System.out.println("参数: " + Arrays.toString(args));
//			}

			int[] ints = sqlServerJdbcTemplate.batchUpdate(sql, batchArgs);
		}

	}

	@Override
	public void deleteQingSaleOrder(String userName, Long user_id) throws Exception {
		String sql = "delete from qingOrder.dbo.t_qing_sale_order " +
				"where USER_ID = ? and TYPE = 'QsaleOrder'";

		// 执行查询
		sqlServerJdbcTemplate.update(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkQingCusBillNo(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_sale_order where CUSBILLNO = '' " +
				"and USER_ID = ? and TYPE = ?";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id,"QsaleOrder");
	}

	@Override
	public List<Map<String, Object>> checkqingCustomer(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_sale_order t1 " +
				"left join AIS20250710155859.dbo.T_BD_CUSTOMER_L t3 " +
				"on t1.CusTomer = t3.fname " +
				"left join AIS20250710155859.dbo.T_BD_CUSTOMER t2 " +
				"on t3.FCUSTID = t2.FCUSTID " +
				"where t1.USER_ID = ?  and t1.TYPE = 'QsaleOrder' " +
				"and (t2.FDOCUMENTSTATUS <> 'C'or t2.FFORBIDSTATUS <> 'A' or t1.CusTomer = '' or t2.FNUMBER is null)";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}


	@Override
	public List<Map<String, Object>> checkqingAccCustomer(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_sale_order t1 " +
				"left join AIS20250710155859.dbo.T_BD_CUSTOMER_L t3 " +
				"on t1.AccCustomer = t3.fname " +
				"left join AIS20250710155859.dbo.T_BD_CUSTOMER t2 " +
				"on t3.FCUSTID = t2.FCUSTID " +
				"where t1.USER_ID = ?  and t1.TYPE = 'QsaleOrder' " +
				"and (t2.FDOCUMENTSTATUS <> 'C'or t2.FFORBIDSTATUS <> 'A' or t1.AccCustomer = '' or t2.FNUMBER is null)";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkqingMaterialNo(Long user_id) throws Exception {
		//连表查询物料物料为空，未审核，或者不是未禁用的，或者金蝶不存在此物料如果查询到数据就说明有问题
		String sql = "select * from qingOrder.dbo.t_qing_sale_order t1 " +
				"left join AIS20250710155859.dbo.t_bd_material t2 " +
				"on t1.MaterialNo = t2.fnumber " +
				"where t1.USER_ID = ?  and t1.TYPE = 'QsaleOrder' " +
				"and (t2.FDOCUMENTSTATUS <> 'C'or t2.FFORBIDSTATUS <> 'A' or t1.MaterialNo = '' or t2.FNUMBER is null)";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkqingCount(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_sale_order t1 " +
				"where t1.USER_ID = ?  and t1.TYPE = 'QsaleOrder' " +
				"and  t1.QTY <= 0 ";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkqingdjCount(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_sale_order t1 " +
				"where t1.USER_ID = ?  and t1.TYPE = 'QsaleOrder' " +
				"and t1.QTY <= 0 and t1.IsGift = 'false'";


		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkqingSheep(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_sale_order " +
				"where (ShipDate = 0 or ShipDate < InDate or InDate = 0 ) " +
				"and USER_ID = ? and TYPE = 'QsaleOrder'";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}
	final int EXCEL_TO_UNIX_DAYS = 25569;
	@Override
	public void saveQingSfcOperp(String userName, Long user_id, List<Map<String, Object>> map)  {
		map = map.subList(1,map.size());
		final int batchSize = 50;
		String sql = "insert into qingOrder.dbo.t_qing_sfc_op " +
				"(MoBillNo,MATERIALNO,Count,DepartName,ORDERTIME,STARTTIME,ENDTIME,LINE,USER_ID,TYPE)" +
				" values (?,?,?,?,?,?,?,?,?,?)";

		for (int i = 0; i < map.size(); i += batchSize) {
			int end = Math.min(i + batchSize, map.size());
			List<Map<String, Object>> subList = map.subList(i, end);
			List<Object[]> batchArgs = new ArrayList<>();
			for (Map<String, Object> row : subList) {

				Object[] args = new Object[]{
						VtuzxUtil.getString(row,"__EMPTY_3"),//指令单号
						VtuzxUtil.getString(row,"__EMPTY_4"),//物料编码
						VtuzxUtil.getDouble(row,"生产订单"),//数量
						VtuzxUtil.getString(row,"__EMPTY_2"),//生产部门
						VtuzxUtil.getInt(row,"__EMPTY_1"),//单据日期
						VtuzxUtil.getInt(row,"__EMPTY_5"),  // STARTTIME
						VtuzxUtil.getInt(row,"__EMPTY_6"),  // ENDTIME
						VtuzxUtil.getInt(row,"line"),//行号
						user_id,
						"QsfcOperp"
				};
				batchArgs.add(args);
			}
			// 打印参数组（可选）
			_logger.info(">>> 插入条数: " + batchArgs.size());
//			for (Object[] args : batchArgs) {
//				System.out.println("参数: " + Arrays.toString(args));
//			}

			int[] ints = sqlServerJdbcTemplate.batchUpdate(sql, batchArgs);
		}
	}
	// 转换工具方法
	private int convertExcelTime(double excelDays) {
		if (excelDays == 0) return 0;  // 保留0值
		return (int) ((excelDays - EXCEL_TO_UNIX_DAYS) * 86400); // 天数转秒
	}
	@Override
	public void deleteQingOperpOrder(String userName, Long user_id) throws Exception {
		String sql = "delete from qingOrder.dbo.t_qing_sfc_op " +
				"where USER_ID = ? and TYPE = 'QsfcOperp'";

		// 执行查询
		sqlServerJdbcTemplate.update(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkQingOperpBillNo(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_sfc_op  where MoBillNo = '' " +
				"and USER_ID = ? and TYPE = ?";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id,"QsfcOperp");
	}

	@Override
	public List<Map<String, Object>> checkQingOperpMaterial(Long user_id) throws Exception {
		//连表查询物料物料为空，未审核，或者不是未禁用的，或者金蝶不存在此物料如果查询到数据就说明有问题
		String sql = "select * from qingOrder.dbo.t_qing_sfc_op t1 " +
				"left join AIS20250710155859.dbo.t_bd_material t2 " +
				"on t1.MATERIALNO = t2.fnumber " +
				"left join AIS20250710155859.dbo.T_BD_MATERIALBASE t3 " +
				"on t2.FMATERIALID = t3.FMATERIALID " +
				"where t1.USER_ID = ?  and t1.TYPE = 'QsfcOperp' " +
				"and (t2.FDOCUMENTSTATUS <> 'C'or t2.FFORBIDSTATUS <> 'A' or t1.MATERIALNO = '' or t2.FNUMBER is null or t3.FISPRODUCE = 0)";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkQingOperpDepart(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_sfc_op t1 " +
				"left join AIS20250710155859.dbo.T_BD_DEPARTMENT_L t3 " +
				"on t1.DepartName = t3.FNAME " +
				"left join AIS20250710155859.dbo.T_BD_DEPARTMENT t2 " +
				"on t2.FDEPTID = t3.FDEPTID " +
				"where t1.USER_ID = ?  and t1.TYPE = 'QsfcOperp' " +
				"and (t2.FDOCUMENTSTATUS <> 'C' or t2.FFORBIDSTATUS <> 'A' or t1.DepartName = '' or t2.FNUMBER is null )";

		System.out.println(sql);
		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkQingOperpStartTime(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_sfc_op where (STARTTIME = 0 or STARTTIME < ORDERTIME or STARTTIME > ENDTIME ) " +
				"and USER_ID = ? and TYPE = 'QsfcOperp'";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkQingOperpEndTime(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_sfc_op " +
				"where (ENDTIME = 0 or ENDTIME < ORDERTIME or STARTTIME > ENDTIME ) " +
				"and USER_ID = ? and TYPE = 'QsfcOperp'";
		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	@Override
	public void saveQingSfcInstock(String userName, Long user_id, List<Map<String, Object>> map) {
		map = map.subList(1,map.size());
		final int batchSize = 50;
		String sql = "insert into qingOrder.dbo.t_qing_sfc_instock " +
				"(MoBillNo,MATERIALNO,Count,Stock,Location,LOT,LINE,USER_ID,TYPE)" +
				" values (?,?,?,?,?,?,?,?,?)";

		for (int i = 0; i < map.size(); i += batchSize) {
			int end = Math.min(i + batchSize, map.size());
			List<Map<String, Object>> subList = map.subList(i, end);
			List<Object[]> batchArgs = new ArrayList<>();
			for (Map<String, Object> row : subList) {

				Object[] args = new Object[]{
						VtuzxUtil.getString(row,"__EMPTY_2"),//指令单号
						VtuzxUtil.getString(row,"__EMPTY_3"),//物料编码
						VtuzxUtil.getDouble(row,"生产入库单"),//数量
						VtuzxUtil.getString(row,"__EMPTY_4"),//仓库
						VtuzxUtil.getString(row,"__EMPTY_5"),//仓位
						VtuzxUtil.getString(row,"__EMPTY_56"),//批号
						VtuzxUtil.getInt(row,"line"),//行号
						user_id,
						"QsfcInstock"
				};
				batchArgs.add(args);
			}
			// 打印参数组（可选）
			_logger.info(">>> 插入条数: " + batchArgs.size());
//			for (Object[] args : batchArgs) {
//				System.out.println("参数: " + Arrays.toString(args));
//			}

			int[] ints = sqlServerJdbcTemplate.batchUpdate(sql, batchArgs);
		}
	}

	@Override
	public void deleteQingsfcInstockOrder(String userName, Long user_id) throws Exception {
		String sql = "delete from qingOrder.dbo.t_qing_sfc_instock " +
				"where USER_ID = ? and TYPE = 'QsfcInstock'";
		// 执行查询
		sqlServerJdbcTemplate.update(sql, user_id);
	}
	@Override
	public List<Map<String, Object>> checkQingInstockBillNo(Long user_id) throws Exception {
		// 修正后的 SQL
		String sql = "SELECT * " +
				" FROM qingOrder.dbo.t_qing_sfc_instock t1 " +  // 正确表别名语法
				" LEFT JOIN  AIS20250710155859.dbo.T_PRD_MORPTENTRY t2 " +
				" ON t1.MoBillNo = t2.FMOBILLNO " +
				" LEFT JOIN AIS20250710155859.dbo.T_PRD_MORPT t3 " +
				" ON t2.FENTRYID = t3.FID " +
				"WHERE (t1.MoBillNo = '' OR t2.FMOBILLNO IS NULL or t3.FDOCUMENTSTATUS <> 'C') " +
				"AND t1.USER_ID = ? " +                        // 第一个参数占位符
				"AND t1.TYPE = 'QsfcInstock'";                // 直接使用固定值

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkQingInstockMaterial(Long user_id) throws Exception {
		//连表查询物料物料为空，未审核，或者不是未禁用的，或者金蝶不存在此物料如果查询到数据就说明有问题
		String sql = "select * from qingOrder.dbo.t_qing_sfc_instock t1 " +
				"left join AIS20250710155859.dbo.t_bd_material t2 " +
				"on t1.MATERIALNO = t2.fnumber " +
				"left join AIS20250710155859.dbo.T_BD_MATERIALBASE t3 " +
				"on t2.FMATERIALID = t3.FMATERIALID " +
				"where  USER_ID = ? and TYPE = 'QsfcInstock' " +
				"and (t2.FDOCUMENTSTATUS <> 'C'or t2.FFORBIDSTATUS <> 'A' or t1.MATERIALNO = '' or t2.FNUMBER is null or t3.FISPRODUCE = 0)";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}


	@Override
	public List<Map<String, Object>> checkQingInstockCount(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_sfc_instock t1 " +
				"LEFT JOIN  AIS20250710155859.dbo.T_PRD_MORPTENTRY t2 " +
				"ON t1.MoBillNo = t2.FMOBILLNO " +
				"WHERE (t1.Count <= 0 ) " +
				"and USER_ID = ? and TYPE = 'QsfcInstock' ";
		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkQingInstockStock(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_sfc_instock t1 " +
				"left join AIS20250710155859.dbo.T_BD_STOCK_L t2 " +
				"on t1.Stock=t2.FNAME " +
				"left join AIS20250710155859.dbo.T_BD_STOCK t3 " +
				"on t2.FStockId  = t3.FStockId " +
				"where (t1.Stock = '' or t3.FDOCUMENTSTATUS <> 'C' or t3.FFORBIDSTATUS <> 'A' OR t3.FNUMBER IS NULL ) " +
				"and USER_ID = ? and TYPE = 'QsfcInstock' ";
		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}
	@Override
	public List<Map<String, Object>> checkQingInstocksumcount(Long user_id) throws Exception {
//		String sql = "SELECT COALESCE(SUM(t3.FREALQTY), 0) AS TotalRealQty, COALESCE(SUM(t2.FQUAQTY), 0) AS TotalQuaQty, " +
//				"COALESCE(SUM(t1.Count), 0) AS TotalCount,t1.MoBillNo " +
//				"FROM qingOrder.dbo.t_qing_sfc_instock t1 " +
//				"LEFT JOIN AIS20250710155859.dbo.T_PRD_MORPTENTRY t2  ON t1.MoBillNo = t2.FMOBILLNO " +
//				"LEFT JOIN AIS20250710155859.dbo.T_PRD_INSTOCKENTRY t3 ON t1.MoBillNo = t3.FMOBILLNO " +
//				"where USER_ID = ? and TYPE = 'QsfcInstock' "+
//				"GROUP BY t1.MoBillNo "+
//				"HAVING (COALESCE(SUM(t1.Count), 0) + COALESCE(SUM(t3.FREALQTY), 0)) > COALESCE(SUM(t2.FQUAQTY), 0) ";
		String sql = "select tt1.TotalCount,tt2.TotalQuaQty,tt3.TotalQuaQty,tt1.MoBillNo from\n" +
				"(select COALESCE(sum(Count), 0) AS TotalCount,MoBillNo from qingOrder.dbo.t_qing_sfc_instock\n" +
				"where USER_ID = ? and TYPE = 'QsfcInstock' \n" +
				"GROUP BY MoBillNo )tt1 \n" +
				"left join\n" +
				"(select COALESCE(SUM(FQUAQTY), 0) AS TotalQuaQty,FMOBILLNO from AIS20250710155859.dbo.T_PRD_MORPTENTRY\n" +
				"GROUP BY FMOBILLNO)tt2 on tt1.MoBillNo = tt2.FMOBILLNO\n" +
				"left join\n" +
				"(select COALESCE(SUM(FREALQTY), 0) AS TotalQuaQty,FMOBILLNO from AIS20250710155859.dbo.T_PRD_INSTOCKENTRY\n" +
				"GROUP BY FMOBILLNO)tt3 on tt1.MoBillNo = tt3.FMOBILLNO\n" +
				"where tt1.TotalCount + tt3.TotalQuaQty > tt2.TotalQuaQty";
		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}
	@Override
	public List<Map<String, Object>> checkQingisLocation(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_sfc_instock t1 " +
				"left join AIS20250710155859.dbo.T_BD_STOCK_L t2 " +
				"on t1.Stock=t2.FNAME " +
				"left join AIS20250710155859.dbo.T_BD_STOCK t3 " +
				"on t2.FStockId  = t3.FStockId " +
				"WHERE (( t3.FIsOpenLocation = 1 and t1.Location = '' ) " +
				"OR ( t3.FIsOpenLocation = 0  and t1.Location <> '' )) " +
				"and USER_ID = ? and TYPE = 'QsfcInstock' ";
		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	@Override
	public void saveQingPurIn(String userName, Long user_id, List<Map<String, Object>> map) {
		map = map.subList(1,map.size());
		final int batchSize = 50;
		String sql = "insert into qingOrder.dbo.t_qing_pur_ins " +
				"(PurBillNo,MoBillNo,MATERIALNO,Count,Stock,Location,LOT,OrderTime,LINE,USER_ID,TYPE)" +
				" values (?,?,?,?,?,?,?,?,?,?,?)";

		for (int i = 0; i < map.size(); i += batchSize) {
			int end = Math.min(i + batchSize, map.size());
			List<Map<String, Object>> subList = map.subList(i, end);
			List<Object[]> batchArgs = new ArrayList<>();
			for (Map<String, Object> row : subList) {

				Object[] args = new Object[]{
						VtuzxUtil.getString(row,"__EMPTY"),//采购订单
						VtuzxUtil.getString(row,"__EMPTY_1"),//采购订单
						VtuzxUtil.getString(row,"__EMPTY_2"),//物料编码
						VtuzxUtil.getDouble(row,"__EMPTY_3"),//数量
						VtuzxUtil.getString(row,"__EMPTY_5"),//仓库
						VtuzxUtil.getString(row,"__EMPTY_6"),//仓位
						VtuzxUtil.getString(row,"__EMPTY_4"),//批号
						VtuzxUtil.getInt(row,"采购入库单"),//单据日期

						VtuzxUtil.getInt(row,"line"),//行号
						user_id,
						"QpurIn"
				};
				batchArgs.add(args);
			}
			// 打印参数组（可选）
			_logger.info(">>> 插入条数: " + batchArgs.size());
//			for (Object[] args : batchArgs) {
//				System.out.println("参数: " + Arrays.toString(args));
//			}

			int[] ints = sqlServerJdbcTemplate.batchUpdate(sql, batchArgs);
		}
	}

	@Override
	public void deleteQingpurInOrder(String userName, Long user_id) throws Exception {
		String sql = "delete from qingOrder.dbo.t_qing_pur_ins " +
				"where USER_ID = ? and TYPE = 'QpurIn'";
		// 执行查询
		sqlServerJdbcTemplate.update(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkQingpurInBillNo(Long user_id) throws Exception {
		// 修正后的 SQL
		String sql = "SELECT * " +
				" FROM qingOrder.dbo.t_qing_pur_ins t1 " +  // 正确表别名语法
				" LEFT JOIN  AIS20250710155859.dbo.T_PUR_POORDER t2 " +
				" ON t1.PurBillNo = t2.FBILLNO " +
				"WHERE (t1.PurBillNo = '' and t2.FBILLNO IS NULL and t2.FDOCUMENTSTATUS <> 'C') " +
				"AND t1.USER_ID = ? " +                        // 第一个参数占位符
				"AND t1.TYPE = 'QpurIn'";                // 直接使用固定值

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkQingpurInMaterial(Long user_id) throws Exception {
		//连表查询物料物料为空，未审核，或者不是未禁用的，或者金蝶不存在此物料如果查询到数据就说明有问题
		String sql = "select * from qingOrder.dbo.t_qing_pur_ins t1 " +
				"left join AIS20250710155859.dbo.t_bd_material t2 " +
				"on t1.MATERIALNO = t2.fnumber " +
				"left join AIS20250710155859.dbo.T_BD_MATERIALBASE t3 " +
				"on t2.FMATERIALID = t3.FMATERIALID " +
				"where  USER_ID = ? and TYPE = 'QpurIn' " +
				"and (t2.FDOCUMENTSTATUS <> 'C'or t2.FFORBIDSTATUS <> 'A' ) and  t1.MATERIALNO = '' and t2.FNUMBER is null";

		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkQingpurInCount(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_pur_ins t1 " +
				"WHERE (t1.Count <= 0 ) " +
				"and USER_ID = ? and TYPE = 'QpurIn' ";
		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}
	@Override
	public List<Map<String, Object>> checkQingMoBillNo(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_pur_ins t1 " +
				"LEFT JOIN AIS20250710155859.dbo.T_STK_INSTOCKENTRY t2 ON t2.F_VKFP_Text_83g = t1.MoBillNo "+
				"WHERE t2.F_VKFP_Text_83g IS NOT NULL " +
				"and t1.MoBillNo IN ( SELECT COALESCE(MoBillNo, '')  FROM qingOrder.dbo.t_qing_pur_ins where USER_ID = ? GROUP BY MoBillNo HAVING COUNT(*) > 1) " +
				"and USER_ID = ? and TYPE = 'QpurIn' ";
		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id,user_id);
	}
	@Override
	public List<Map<String, Object>> checkQingpurorderTime(Long user_id) throws Exception {
		String sql = "SELECT * FROM qingOrder.dbo.t_qing_pur_ins t1 " +
				"LEFT JOIN AIS20250710155859.dbo.T_PUR_POORDER t2 ON t1.PurBillNo = t2.FBILLNO " +
				"WHERE (t1.OrderTime = 0 OR " +
				"       CONVERT(DATETIME, DATEADD(DAY, t1.OrderTime, '1899-12-30')) < t2.FDATE) " +
				"AND USER_ID = ? AND TYPE = 'QpurIn'";

		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}
	@Override
	public List<Map<String, Object>> checkQingpurInStock(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_pur_ins t1 " +
				"left join AIS20250710155859.dbo.T_BD_STOCK_L t2 " +
				"on t1.Stock=t2.FNAME " +
				"left join AIS20250710155859.dbo.T_BD_STOCK t3 " +
				"on t2.FStockId  = t3.FStockId " +
				"where (t3.FDOCUMENTSTATUS <> 'C' or t3.FFORBIDSTATUS <> 'A') and t3.FNUMBER IS NULL and t1.Stock = '' " +
				"and USER_ID = ? and TYPE = 'QpurIn' ";
		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

	@Override
	public List<Map<String, Object>> checkQingpurisLocation(Long user_id) throws Exception {
		String sql = "select * from qingOrder.dbo.t_qing_pur_ins t1 " +
				"left join AIS20250710155859.dbo.T_BD_STOCK_L t2 " +
				"on t1.Stock=t2.FNAME " +
				"left join AIS20250710155859.dbo.T_BD_STOCK t3 " +
				"on t2.FStockId  = t3.FStockId " +
				"WHERE (( t3.FIsOpenLocation = 1 and t1.Location = '' ) " +
				"OR ( t3.FIsOpenLocation = 0  and t1.Location <> '' )) " +
				"and USER_ID = ? and TYPE = 'QpurIn' ";
		// 执行查询
		return sqlServerJdbcTemplate.queryForList(sql, user_id);
	}

}